package com.wetool.jpa.invoicing;

import java.math.BigDecimal;
import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.wetool.entity.Commodity;
import com.wetool.entity.invoicing.InvoicingCommodity;
import com.wetool.entity.invoicing.Supplier;

@Repository
public interface InvoicingCommodityRepo extends JpaRepository<InvoicingCommodity, Long>, JpaSpecificationExecutor<InvoicingCommodity> {

	@Query("select count(t.commodity.barcode) from InvoicingCommodity t where t.shopId = :shopId and t.isDeleted = false")
	Long countByBarcodeAndShopId(@Param("shopId")Long shopId);

	@Query("select sum(COALESCE(t.inventoryNumber,0)) from InvoicingCommodity t where t.commodity.type = :type and t.shopId = :shopId and t.isDeleted = false")
	BigDecimal getByQuantityAndShopId(@Param("type") Long type,@Param("shopId")Long shpId);
	
	@Query("select SUM(COALESCE(t.buyingPrice,0) * COALESCE(t.inventoryNumber,0)) from InvoicingCommodity t where t.shopId = :shopId and t.isDeleted = false")
	BigDecimal getInventoryCost(@Param("shopId")Long shpId);
	
	@Query("select SUM(COALESCE(t.sellingPrice,0) * COALESCE(t.inventoryNumber,0)) from InvoicingCommodity t where t.shopId = :shopId and t.isDeleted = false")
	BigDecimal getAdvanceCost(@Param("shopId")Long shpId);

	@Query("select ic from InvoicingCommodity ic where ic.commodity.id = :id and ic.shopId = :shopId and ic.isDeleted = :isDeleted")
	InvoicingCommodity findOneByCommodityAndShopIdAndIsDeleted(@Param("id")Long id, @Param("shopId")Long shopId, @Param("isDeleted")boolean isDeleted);
	
	@Query("select s from InvoicingCommodity ic , Supplier s where ic.supplierId = s.id and ic.commodity.id = :id and ic.shopId = :shopId and ic.isDeleted = false")
	Supplier getSupplierByCommodityAndShopId(@Param("id")Long id, @Param("shopId")Long shopId);

	@Query("select ic from InvoicingCommodity ic where ic.commodity = :commodity")
	List<InvoicingCommodity> getByCommodity(@Param("commodity")Commodity commodity);

	@Query("select ic from InvoicingCommodity ic where ic.commodity.id = :id and ic.shopId = :shopId and ic.isDeleted = false")
	InvoicingCommodity getCommodityAndShopId(@Param("id")Long commodityId, @Param("shopId")Long shopId);

}
